package phoenix.core.helpers.extensions;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.lang3.StringEscapeUtils;
import org.testng.TestException;

import phoenix.core.PhoenixManager;
import phoenix.core.data_models.TestUserDataModel;
import core.ApplicationManager;
import core.data_models.TestDataModel;
import core.helpers.DBHelper;

/**
 * Class extends the methods for working with database
 */
public class DBHelperExt extends DBHelper
{
	// PhoenixManager instance
	protected static PhoenixManager phoenix;

	/**
	 * Initializes DBHelperExt
	 * @param p_manager
	 *            ApplicationManager instance
	 * @param p_phoenix
	 *            PhoenixManager instance
	 */
	public DBHelperExt(ApplicationManager p_manager, PhoenixManager p_phoenix)
	{
		super(p_manager);
		phoenix = p_phoenix;
	}

	/**
	 * Gets site ID by site name
	 * @param p_siteName
	 *            Site name
	 * @return SIte ID
	 */
	public String getSiteName(String p_siteName)
	{
		phoenix.log().debug("Get site ID from DB => p_siteName=" + p_siteName);
		String siteName = "";

		try
		{
			if (!p_siteName.isEmpty())
			{
				querySelect("SELECT site_name FROM sites WHERE site_name='" + p_siteName + "'");

				while (queryResult.next())
				{
					siteName = queryResult.getString("site_name");
					break;
				}
			}
			else
			{
				phoenix.log().warn("Site id not found. Site name is empty.");
			}
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get site ID from DB: " + p_ex);
		}

		return siteName;
	}

	/**
	 * Gets site URL
	 * @param p_siteName
	 *            Site name
	 * @param p_build
	 *            Site build
	 * @return Site URL
	 */
	public String getSiteURL(String p_siteName, String p_build)
	{
		String siteURL = "";

		if (p_siteName != null && !p_siteName.equals("") && !p_build.isEmpty())
		{
			phoenix.log().debug("Get site URL from DB => p_siteName=" + p_siteName + "; p_build=" + p_build);
			queryResult = querySelect("SELECT url_" + p_build + " FROM sites WHERE site_name='" + p_siteName + "'");

			try
			{
				while (queryResult.next())
				{
					siteURL = queryResult.getString("url_" + p_build);

					break;
				}
			}
			catch (Exception p_ex)
			{
				phoenix.log().warn("Cannot get site URL from DB: " + p_ex);
			}
		}
		else
		{
			phoenix.log().error("Site URL not found in DB");
		}

		return siteURL;
	}

	/**
	 * Gets first names by country, gender
	 * @param p_country
	 *            Country name
	 * @param p_gender
	 *            User gender
	 * @return List of first names
	 */
	public String[] getFirstNames(String p_country, String p_gender)
	{
		phoenix.log().debug("Get first names from DB => p_country=" + p_country + "; p_gender=" + p_gender);
		String firstNames = "";
		String[] firstNamesList = null;

		if (p_country == null)
		{
			p_country = "%%%";
		}

		querySelect("SELECT first_name FROM first_names WHERE country LIKE '" + p_country + "' AND gender='" + p_gender
			+ "' ORDER BY RAND() LIMIT 10");
		try
		{
			while (queryResult.next())
			{
				firstNames += queryResult.getString("first_name") + ",";
			}

			firstNamesList = firstNames.split(",");

			return firstNamesList;
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get first names from DB: " + p_ex);
		}

		return null;
	}

	/**
	 * Gets list of cities by country name
	 * @param p_country
	 *            Country name
	 * @return List of city names
	 */
	public String[] getCities(String p_country)
	{
		phoenix.log().debug("Get city from DB => p_country=" + p_country);
		String cities = "";
		String[] cityList = null;
		querySelect("SELECT city FROM cities WHERE country='" + p_country + "' ORDER BY RAND() LIMIT 10");

		try
		{
			while (queryResult.next())
			{
				cities += queryResult.getString("city") + ",";
			}

			cityList = cities.split(",");

			return cityList;
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get list of cities: " + p_ex);
		}

		return null;
	}

	/**
	 * Gets list of users by site id
	 * @param p_siteName
	 *            Site name
	 * @return list of users
	 */
	public TestUserDataModel[] getUsers(String p_siteName)
	{
		phoenix.log().debug("Get user from DB => p_siteName=" + p_siteName);
		TestUserDataModel[] usersList = null;
		querySelect("SELECT * FROM users WHERE site_name='" + p_siteName + "' AND uid NOT LIKE '00000000000000000000000000000000'"
			+ " ORDER BY RAND() LIMIT 10");
		int i = 0;
		usersList = new TestUserDataModel[getRowCount(queryResult)];

		try
		{
			while (queryResult.next())
			{
				usersList[i] = fillUser(queryResult);
				i++;
			}

			return usersList;
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get user list by siteName: " + p_ex);
		}

		return null;
	}

	/**
	 * Gets list of users by site id and country
	 * @param p_siteName
	 * @param p_country
	 * @return list of users
	 */
	public TestUserDataModel[] getUsers(String p_siteName, String p_country)
	{
		phoenix.log().debug("Get user from DB => p_siteName=" + p_siteName);
		TestUserDataModel[] usersList = null;
		querySelect("SELECT * FROM users WHERE site_name='" + p_siteName + "' AND country='" + p_country
			+ "' AND uid NOT LIKE '00000000000000000000000000000000'" + " ORDER BY RAND() LIMIT 10");
		int i = 0;
		usersList = new TestUserDataModel[getRowCount(queryResult)];

		try
		{
			while (queryResult.next())
			{
				usersList[i] = fillUser(queryResult);
				i++;
			}

			return usersList;
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get user list by siteName: " + p_ex);
		}

		return null;
	}

	/**
	 * Gets list of users by site name and membership status
	 * @param p_siteName
	 *            Site name
	 * @param p_membershipStatus
	 *            Membership status
	 * @return list of users
	 */
	public TestUserDataModel[] getUsers(String p_siteName, String p_membershipStatus, String p_gender)
	{
		phoenix.log().info("Get user from DB => p_siteName=" + p_siteName + "p_membershipStatus=" + p_membershipStatus);
		TestUserDataModel[] usersList = null;
		String qry_pref = "SELECT * FROM users WHERE site_name='" + p_siteName + "' AND membership_status='" + p_membershipStatus;
		String qry_postf = " ORDER BY RAND() LIMIT 15";
		String qry_mid = "";
		if (p_gender.length() > 0)
		{
			qry_mid = " AND gender='" + p_gender + "'";
		}
		querySelect(qry_pref + qry_mid + qry_postf);
		int i = 0;
		usersList = new TestUserDataModel[getRowCount(queryResult)];

		try
		{
			while (queryResult.next())
			{
				usersList[i] = fillUser(queryResult);
				i++;
			}

			return usersList;
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get user list by siteName and membershipStatus: " + p_ex);
		}

		return null;
	}

	/**
	 * Gets list of post codes by country name and city name
	 * @param p_country
	 *            Contry code
	 * @param p_city
	 *            City name
	 * @return List of post codes
	 */
	public String[] getPostcodes(String p_country, String p_city)
	{
		phoenix.log().debug("Get city from DB => p_country=" + p_country + "; p_city=" + p_city);

		String postcodes = "";
		String[] postcodeList = null;
		querySelect("SELECT postcode FROM cities WHERE country='" + p_country + "' AND city='" + p_city + "' ORDER BY RAND() LIMIT 10");

		try
		{
			while (queryResult.next())
			{
				postcodes += queryResult.getString("postcode") + ",";
			}

			postcodeList = postcodes.split(",");

			return postcodeList;
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get city from DB: " + p_ex);
		}

		return null;
	}

	/**
	 * Adds test data
	 * @param p_testDataModel
	 *            TestDataModel instance
	 */
	public void addTestData(TestDataModel p_testDataModel)
	{
		phoenix.log().debug("Add test data to DB");
		query("INSERT INTO tests (test_name, site_name, browser, location, date_start, date_end, user_email, status, time_run, redmine_id, platform, build) "
			+ "VALUES ('"
			+ p_testDataModel.getTestName() + "', '"
			+ p_testDataModel.getSiteName() + "','"
			+ p_testDataModel.getBrowserName() + "','"
			+ p_testDataModel.getLocation() + "', '"
			+ p_testDataModel.getDateStart() + "', '"
			+ p_testDataModel.getDateEnd() + "','"
			+ phoenix.testUser().getEmail() + "','"
			+ p_testDataModel.getStatus() + "', '"
			+ p_testDataModel.getTimeRun() + "', '"
			+ p_testDataModel.getRedmineID() + "', '"
			+ p_testDataModel.getPlatform() + "', '"
			+ p_testDataModel.getBuild() + "')");
	}

	/**
	 * Updates test status
	 * @param p_testDataModel
	 *            TestDataModel instance
	 */
	public void updateTestData(TestDataModel p_testDataModel)
	{
		phoenix.log().debug("Update test data");
		query("UPDATE tests set "
			+ "test_name='" + p_testDataModel.getTestName() + "',"
			+ "site_name='" + p_testDataModel.getSiteName() + "',"
			+ "browser='" + p_testDataModel.getBrowserName() + "',"
			+ "location='" + p_testDataModel.getLocation() + "',"
			+ "date_start='" + p_testDataModel.getDateStart() + "',"
			+ "date_end='" + p_testDataModel.getDateEnd() + "',"
			+ "user_email='" + phoenix.testUser().getEmail() + "',"
			+ "status='" + p_testDataModel.getStatus() + "',"
			+ "time_run='" + p_testDataModel.getTimeRun() + "',"
			+ "redmine_id='" + p_testDataModel.getRedmineID() + "',"
			+ "platform='" + p_testDataModel.getPlatform() + "' "
			+ "WHERE date_start='" + p_testDataModel.getDateStart() + "' AND test_name='" + p_testDataModel.getTestName() + "' AND build='"
			+ p_testDataModel.getBuild() + "'");
	}

	/**
	 * @param p_testName
	 * @param p_hours
	 * @param p_platform
	 * @return Boolean / TRUE = no fails / FALSE = test has failed
	 */
	public Boolean checkTestStatusForLastHours(String p_testName, Integer p_hours, String p_platform)
	{
		phoenix.log().debug("Check status for test " + p_testName + " for last " + p_hours + " hours, at platform " + p_platform);

		try
		{
			// Check if there are fails
			ResultSet results = querySelect(
				"SELECT * FROM tests WHERE platform = '" + p_platform + "' AND build = 'live' AND test_name = '" + p_testName
					+ "' AND date_end >= DATE_SUB(NOW(),INTERVAL "
					+ p_hours + "  HOUR) AND status = '0' ");

			if (results.next())
			{
				// fails does exist
				return false;
			}

			// Check if tests working
			results = querySelect(
				"SELECT * FROM tests WHERE platform = '" + p_platform + "' AND build = 'live' AND test_name = '" + p_testName
					+ "' AND date_end >= DATE_SUB(NOW(),INTERVAL "
					+ p_hours + "  HOUR) AND status = '1' ");

			if (results.next())
			{
				// Test does work
				return true;
			}

		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Something went wrong in checking test status at DB");
		}

		return false;
	}

	/**
	 * Updates user hidden status
	 * @param p_testUser
	 *            TestUserDataModel instance
	 * @param p_newHiddenStatus
	 *            User hidden status
	 */
	public void updateUserHiddenStatus(TestUserDataModel p_testUser, String p_newHiddenStatus)
	{
		phoenix.log().debug("Update user hidden status: " + p_testUser.getHiddenStatus() + " => " + p_newHiddenStatus);
		query("UPDATE users set + hidden_status=" + p_newHiddenStatus + "' WHERE email ='" + p_testUser.getEmail() + "'");
	}

	/**
	 * Gets user confirmed
	 * @return TestUserModel instance
	 */
	public TestUserDataModel getUser(String p_userID)
	{
		phoenix.log().info("Get test user from DB => p_userID=" + p_userID);
		TestUserDataModel testUser = null;
		querySelect("SELECT * FROM users WHERE " + "uid='" + p_userID + "'");

		try
		{
			if (getRowCount(queryResult) > 0)
			{
				while (queryResult.next())
				{
					testUser = fillUser(queryResult);
					return testUser;
				}
			}
			else
			{
				phoenix.log().error("Unable to fill user " + p_userID);
			}
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get user from DB: " + p_ex);
		}

		return testUser;
	}

	/**
	 * Fills user data model
	 * @return TestUserModel instance
	 */
	private TestUserDataModel fillUser(ResultSet p_queryResult)
	{
		TestUserDataModel user = null;

		try
		{
			phoenix.log().debug("Fill user data => userID=" + p_queryResult.getString("uid"));

			user = new TestUserDataModel(phoenix);
			user.setId(p_queryResult.getString("uid"))
				.setEmail(p_queryResult.getString("email"))
				.setScreenname(p_queryResult.getString("screenname"))
				.setPassword(p_queryResult.getString("password"))
				.setGender(p_queryResult.getString("gender"))
				.setSexuality(p_queryResult.getString("sexuality"))
				.setBirthDate(p_queryResult.getString("birth_date"))
				.setCountry(p_queryResult.getString("country"))
				.setCity(p_queryResult.getString("city"))
				.setPostcode(StringEscapeUtils.unescapeEcmaScript(p_queryResult.getString("postcode")))
				.setMembershipStatus(Integer.parseInt(p_queryResult.getString("membership_status")))
				.setSiteName(p_queryResult.getString("site_name"))
				.setUserKey(p_queryResult.getString("user_key"))
				.setRegSource(p_queryResult.getString("reg_source"))
				.setRegDate(p_queryResult.getString("reg_date"))
				.setConfirmDate(p_queryResult.getString("confirm_date"))
				.setPlatform(p_queryResult.getString("platform"))
				.setSiteId(p_queryResult.getString("site_id"))
				.setBuild("build");

		}
		catch (SQLException p_ex)
		{
			phoenix.log().warn("Cannot fill user data model: " + p_ex);
		}

		return user;
	}

	/**
	 * Updates user data
	 * @param p_user
	 *            TestUserDataModel instance
	 */
	public void updateUserData(TestUserDataModel p_user)

	{
		phoenix.log().debug("Update user data");
		query("UPDATE users set "
			+ "uid='" + p_user.getId() + "', "
			+ "screenname='" + p_user.getScreenname() + "', "
			+ "password='" + p_user.getPassword() + "', "
			+ "gender='" + p_user.getGender() + "', "
			+ "sexuality='" + p_user.getSexuality() + "', "
			+ "birth_date='" + p_user.getBirthDate() + "', "
			+ "country='" + p_user.getCountry() + "', "
			+ "city='" + p_user.getCity() + "', "
			+ "postcode='" + StringEscapeUtils.escapeEcmaScript(p_user.getPostcode()) + "', "
			+ "membership_status='" + p_user.getMembershipStatus() + "', "
			+ "site_name='" + p_user.getSiteName() + "', "
			+ "user_key='" + p_user.getUserKey() + "', "
			+ "reg_source='" + p_user.getRegSource() + "', "
			+ "reg_date='" + p_user.getRegDate() + "', "
			+ "platform='" + p_user.getPlatform() + "', "
			+ "site_id='" + p_user.getSiteId() + "', "
			+ "build='" + p_user.getBuild() + "' "
			+ "WHERE email='" + p_user.getEmail() + "'");
	}

	/**
	 * Updates user confirm date
	 * @param p_user
	 *            TestUserDataModel instance
	 */
	public void updateUserConfirmDate(TestUserDataModel p_user)
	{
		phoenix.log().debug("Update user data");
		query("UPDATE users set "
			+ "confirm_date='" + p_user.getConfirmDate() + "' "
			+ "WHERE email='" + p_user.getEmail() + "'");
	}

	/**
	 * Add new user data
	 * @param p_user
	 *            TestUserDataModel instance
	 */
	public void addUserData(TestUserDataModel p_user)
	{
		phoenix.log().debug("Update user data");

		query(
			"INSERT INTO users (uid, email, screenname, password, gender, sexuality, birth_date, country, city, postcode, membership_status, site_name, user_key, reg_source, reg_date, confirm_date, hidden_status, platform, site_id, build) VALUES ("
				+ "'"
				+ p_user.getId() + "', "
				+ "'" + p_user.getEmail() + "', "
				+ "'" + p_user.getScreenname() + "', "
				+ "'" + p_user.getPassword() + "', "
				+ "'" + p_user.getGender() + "', "
				+ "'" + p_user.getSexuality() + "', "
				+ "'" + p_user.getBirthDate() + "', "
				+ "'" + p_user.getCountry() + "', "
				+ "'" + p_user.getCity() + "', "
				+ "'" + StringEscapeUtils.escapeEcmaScript(p_user.getPostcode()) + "', "
				+ "'" + p_user.getMembershipStatus() + "', "
				+ "'" + p_user.getSiteName() + "', "
				+ "'" + p_user.getUserKey() + "', "
				+ "'" + p_user.getRegSource() + "', "
				+ "'" + p_user.getRegDate() + "', "
				+ "'0000-00-00 00:00:00', "
				+ "'" + p_user.getHiddenStatus() + "', "
				+ "'" + p_user.getPlatform() + "', "
				+ "'" + p_user.getSiteId() + "', "
				+ "'" + p_user.getBuild() + "')");

	}

	/**
	 * Checks for user is exist in DB
	 * @param p_user
	 *            TestUserDataModel instance
	 */
	public boolean isUserExist(TestUserDataModel p_user, boolean p_trowable)
	{
		phoenix.log().debug("Check for is user exist in DB");
		querySelect("SELECT * FROM users WHERE uid='" + p_user.getId() + "'");

		if (getRowCount(queryResult) > 0)
		{
			try
			{
				return true;
			}
			catch (Exception p_ex)
			{
				phoenix.log().warn("Cannot get user from DB: " + p_ex);
				return false;
			}
		}
		else
		{
			phoenix.log().warn("Test user not found in DB");

			if (p_trowable)
			{
				throw new TestException("Test user not found in DB " + p_user.getEmail());
			}

			return false;
		}
	}

	public TestUserDataModel getUserByMembershipStatus(String p_siteName, Integer p_membershipStatus)
	{
		ResultSet result = null;
		phoenix.log().debug("Get user from DB => p_siteName=" + p_siteName + "p_membershipStatus=" + p_membershipStatus);
		TestUserDataModel user = new TestUserDataModel(phoenix);

		String query = "SELECT * FROM users WHERE site_name='" + p_siteName + "' AND membership_status='" + p_membershipStatus
			+ "' AND hidden_status ='0' AND country='" + manager.testModel().getLocation() + "'";

		result = querySelect(query + " LIMIT 1");

		try
		{
			queryResult.next();
			return fillUser(result);

		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get user list by siteName and membershipStatus: " + p_ex);
		}
		return null;
	}

	/**
	 * Custom getter for TestUserDataModel
	 * @param p_siteName
	 * @param p_membershipStatus
	 * @param p_gender
	 * @return
	 */
	public TestUserDataModel getUserBy(String p_siteName, Integer p_membershipStatus, String p_gender)
	{
		ResultSet result = null;
		phoenix.log().debug("Get user from DB => p_siteName=" + p_siteName + "p_membershipStatus=" + p_membershipStatus);
		TestUserDataModel user = new TestUserDataModel(phoenix);
		result = querySelect("SELECT * FROM users WHERE site_name='" + p_siteName + "' AND membership_status='" + p_membershipStatus
			+ "' AND hidden_status ='0' AND gender = '" + p_gender + "' LIMIT 1");

		try
		{
			queryResult.next();
			return fillUser(result);

		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("getUserBy: " + p_ex);
		}
		return null;
	}

	/**
	 * Custom getter for TestUserDataModel
	 * @param p_siteName
	 * @param p_membershipStatus
	 * @param p_gender
	 * @return
	 */
	public TestUserDataModel getUserBy(String p_siteName, Integer p_membershipStatus, String p_gender, String p_registeredHours)
	{
		phoenix.log().debug("Get user from DB => p_siteName=" + p_siteName + "p_membershipStatus=" + p_membershipStatus);
		String r_platform = (manager.testModel().getPlatform().equals("web")) ? "webSite" : "mobSite";

		ResultSet result = querySelect("SELECT * FROM users WHERE site_name='" + p_siteName + "' AND membership_status='" + p_membershipStatus
			+ "' AND hidden_status ='0' AND gender = '" + p_gender + "' AND platform = '" + r_platform
			+ "' AND build = " + manager.testModel().getBuild() + " AND reg_date >= DATE_SUB(NOW(),INTERVAL " + p_registeredHours
			+ " HOUR)  ORDER BY reg_date LIMIT 1");

		try
		{
			queryResult.next();
			return fillUser(result);

		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("getUserBy: " + p_ex);
		}
		return null;
	}

	public TestUserDataModel getUserBy(String p_siteName, String p_gender, Integer p_numberOfPhotos)
	{
		ResultSet result = null;
		phoenix.log().debug("Get user from DB => p_siteName=" + p_siteName + "p_gender=" + p_gender + " p_numberOfPhotos = " + p_numberOfPhotos);
		String r_platform = (manager.testModel().getPlatform().equals("web")) ? "webSite" : "mobSite";
		TestUserDataModel user = new TestUserDataModel(phoenix);
		result = querySelect("SELECT * FROM users WHERE site_name='" + p_siteName + "' AND hidden_status ='0' AND gender = '" + p_gender + "' AND photos = '"
			+ p_numberOfPhotos + "' AND platform = '" + r_platform + "' AND build = " + manager.testModel().getBuild() + " LIMIT 1");

		try
		{
			queryResult.next();
			return fillUser(result);

		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("getUserBy: " + p_ex);
		}
		return null;
	}

	public TestUserDataModel getUserBy(String p_registeredHours, Integer p_membershipStatus)
	{
		ResultSet result = null;
		phoenix.log().debug("Get user from DB => p_membershipStatus=" + p_membershipStatus + " p_registeredHours = " + p_registeredHours);
		String r_platform = (manager.testModel().getPlatform().equals("web")) ? "webSite" : "mobSite";
		TestUserDataModel user = new TestUserDataModel(phoenix);
		result = querySelect(
			"SELECT * FROM users WHERE site_name = '" + manager.testModel().getSiteName() + "' AND hidden_status ='0' AND platform = '" + r_platform +
				"' AND reg_date >= DATE_SUB(NOW(),INTERVAL " + p_registeredHours + " HOUR) AND membership_status = '" + p_membershipStatus + "' LIMIT 1");
		try
		{
			queryResult.next();
			return fillUser(result);
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("getUserBy: " + p_ex);
		}
		return null;
	}

	public String getUserIDByGender(String p_siteName, String p_gender)
	{
		ResultSet result = null;
		phoenix.log().debug("Get user from DB => p_siteName=" + p_siteName + "p_membershipStatus=" + p_gender);
		result = querySelect("SELECT uid FROM users WHERE site_name='" + p_siteName + "' AND gender='" + p_gender
			+ "' AND hidden_status ='0' LIMIT 1");

		try
		{
			queryResult.next();
			return result.getString("uid");
		}
		catch (Exception p_ex)
		{
			phoenix.log().warn("Cannot get user list by siteName and gender " + p_ex);
		}
		return null;
	}
}